﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SQLite;

namespace WindowsFormsApplication1
{
    public partial class Form4 : Form
    {
        public Form4()
        {
            InitializeComponent();
        }

        SQLiteConnection conn;
        SQLiteDataAdapter sda;
        DataSet ds;

        private void button1_Click(object sender, EventArgs e)
        {
            SQLiteCommand cmd = new SQLiteCommand("select * from test_book", conn);
            sda = new SQLiteDataAdapter();
            sda.SelectCommand = cmd;
            ds = new DataSet();
            sda.Fill(ds, "test3");
            dataGridView1.DataSource = ds.Tables[0];
        }

        private void Form4_Load(object sender, EventArgs e)
        {
            string conString = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
            conn = new SQLiteConnection(conString);
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
            textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
            textBox3.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
            textBox4.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
            textBox5.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
            textBox6.Text = dataGridView1.Rows[e.RowIndex].Cells[5].Value.ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string isbn = textBox1.Text;
            string bookname = textBox2.Text;
            string author = textBox3.Text;
            string price = textBox4.Text.ToString();
            string press = textBox5.Text;
            string revision = textBox6.Text.ToString();
            string instring = "insert into test_book (isbn,bookname,author,price,press,revision) values ('" + isbn + "','" + bookname + "','" + author + "'," + price + ",'" + press + "'," + revision + " )";
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(instring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test3");
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string isbn = textBox1.Text;
            string bookname = textBox2.Text;
            string author = textBox3.Text;
            string price = textBox4.Text.ToString();
            string press = textBox5.Text;
            string revision = textBox6.Text.ToString();
            string upstring = "update test_book set isbn ='" + isbn + "', bookname ='" + bookname + "', author ='" + author + "', price =" + price + ", press ='" + press + "', revision =" + revision + " where isbn = " + isbn;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(upstring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test3");
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string isbn = textBox1.Text.ToString();
            string destring = "delete from test_book where isbn =" + isbn;
            conn.Open();
            SQLiteCommand cmd = new SQLiteCommand(destring, conn);
            cmd.ExecuteNonQuery();
            conn.Close();
            ds.Clear();
            sda.Fill(ds, "test3");
        }
    }
}
